from sqlalchemy import and_, or_, event, func, desc
from .models import *
from sqlalchemy.orm import sessionmaker, joinedload
from common import config, exceptions, utils
from datetime import datetime, timedelta
from contextlib import contextmanager

@contextmanager
def get_session(debug=False):
    session = sessionmaker(bind=engine)()
    if debug:
        def before_cursor_execute(conn, cursor, statement, parameters, context, executemany):
            print(f"SQL: {statement}, Parameters: {parameters}")

        event.listen(session.bind, "before_cursor_execute", before_cursor_execute)
        session._debug_sql_listener = before_cursor_execute
    try:
        yield session
    finally:
        if hasattr(session, "_debug_sql_listener"):
            event.remove(session.bind, "before_cursor_execute", session._debug_sql_listener)
        session.close()

def login(username, password):
    with get_session() as session:
        user = session.query(User).filter(User.username == username, User.password == utils.encrypt_password(password)).first()
        user = user.to_dict(include_relationships=False) if user else None
        if user:
            user.pop("password")
        return user
    
def get_users(username="", role=None, status=None, page=1, per_page=config.DEFAULT_PAGE_SIZE):
    with get_session() as session:
        query = session.query(User)
        if username:
            query = query.filter(User.username.like(f'%{username}%'))
        if role:
            query = query.filter(User.role == role)
        if status is not None:
            query = query.filter(User.status == status)
        count = query.count()
        if page and per_page:
            query = query.offset((page - 1) * per_page).limit(per_page)
        users = [user.to_dict(include_relationships=False) for user in query.all()]
        for user in users:
            user.pop("password")
        return {
            'count': count,
            'items': users,
            'page': page,
            'per_page': per_page
        }

def get_user(user_id):
    with get_session() as session:
        return session.query(User).filter(User.id == user_id).first().to_dict()

def add_user(username, password, role, status=True):
    with get_session() as session:
        user = User(username=username, password=utils.encrypt_password(password), role=role, status=status)
        session.add(user)
        session.commit()
        return user.to_dict()

def update_user(id, username="", password="", role="", status=None):
    with get_session() as session:
        user = session.query(User).filter(User.id == id).first()
        if not user:
            return
        if username:
            user.username = username
        if password:
            user.password = utils.encrypt_password(password)
        if role:
            user.role = role
        if status is not None:
            user.status = status
        session.commit()
        return user.to_dict()

def delete_user(user_id):
    with get_session() as session:
        user = session.query(User).filter(User.id == user_id).first()
        if not user:
            return
        if session.query(Purchase).filter(Purchase.user_id == user.id).count() > 0:
            raise exceptions.DBQueryException("该用户下有采购单，禁止删除")
        if session.query(Sale).filter(Sale.user_id == user.id).count() > 0:
            raise exceptions.DBQueryException("该用户下有销售单，禁止删除")
        session.delete(user)
        session.commit()
        return user.to_dict()

def get_products(code, name, category_id, status=True, low_stock_only=False, page=1, per_page=config.DEFAULT_PAGE_SIZE, order_by=""):
    with get_session() as session:
        query = session.query(Product)
        if code:
            query = query.filter(Product.code.like(f'%{code}%'))
        if name:
            query = query.filter(Product.name.like(f'%{name}%'))
        if category_id:
            all_category = get_categories(category_id)
            query = query.filter(Product.category_id.in_([category['id'] for category in all_category]))
        if status is not None:
            query = query.filter(Product.status == status)
        if low_stock_only:
            query = query.filter(Product.stock <= Product.lowest_stock)
        only_relationships = ["category"]
        count = query.count()
        if order_by:
            if order_by.startswith("-"):
                order_by = order_by[1:]
                if order_by in config.PRODUCT_ALLOW_SORT_COLUMNS:
                    query = query.order_by(desc(order_by))
            else:
                if order_by in config.PRODUCT_ALLOW_SORT_COLUMNS:
                    query = query.order_by(order_by)
        if page and per_page:
            query = query.offset((page - 1) * per_page).limit(per_page)
            query = query.options(joinedload(Product.category))
        return {
            'count': count,
            'items': [product.to_dict(only_relationships=only_relationships) for product in query.all()],
            'page': page,
            'per_page': per_page
        }

def get_product(id_or_code):
    with get_session() as session:
        if utils.match_product_code(id_or_code):
            product = session.query(Product).filter(Product.code == id_or_code).first()
        else:
            product = session.query(Product).filter(Product.id == id_or_code).first()
        return product.to_dict(include_relationships=False) if product else None

def add_product(name, category_id, unit, price, stock, lowest_stock=0, description="", status=True):
    with get_session() as session:
        product = Product(code=utils.product_code_generator(), name=name, category_id=category_id,
            unit=unit, price=price, stock=stock, lowest_stock=lowest_stock,
            description=description, status=status)
        session.add(product)
        session.commit()
        return product.to_dict(include_relationships=False)

def update_product(code, name, category_id, unit, price, lowest_stock, description, status):
    with get_session() as session:
        product = session.query(Product).filter(Product.code == code).first()
        if not product:
            return
        if name:
            product.name = name
        if category_id:
            product.category_id = category_id
        if unit:
            product.unit = unit
        if price:
            product.price = price
        if lowest_stock:
            product.lowest_stock = lowest_stock
        if description:
            product.description = description
        if status is not None:
            product.status = status
        product.updated_at = datetime.now()
        session.commit()
        return product.to_dict(only_relationships=["category"])

def delete_product(code):
    with get_session() as session:
        product = session.query(Product).filter(Product.code == code).first()
        if not product:
            return
        if session.query(PurchaseItem).filter(PurchaseItem.product_id == product.id).count() > 0:
            raise exceptions.DBQueryException("该商品已关联采购单，禁止删除")
        if session.query(SaleItem).filter(SaleItem.product_id == product.id).count() > 0:
            raise exceptions.DBQueryException("该商品已关联销售单，禁止删除")
        session.delete(product)
        session.commit()
        return product.to_dict(include_relationships=False)

def get_categories(parent_id=None):
    "如果指定parent_id, 则会递归查询所有子分类"
    with get_session() as session:
        query = session.query(Category)
        if parent_id is not None:
            # 使用CTE递归查询所有子分类
            cte = session.query(Category).filter(
                Category.parent_id == parent_id
            ).cte(name='cte', recursive=True)
            
            # 递归部分：将子分类与父分类连接
            children = session.query(Category).join(
                cte, Category.parent_id == cte.c.id
            )
            
            # 组合父分类和所有子分类
            cte = cte.union_all(children)
            
            # 获取包括父分类在内的所有分类
            query = session.query(Category).filter(
                or_(Category.id == parent_id, Category.id.in_(
                    session.query(cte.c.id)
                ))
            )
        return [category.to_dict(include_relationships=False) for category in query.all()]
        

def get_category(id):
    with get_session() as session:
        category = session.query(Category).filter(Category.id == id).first()
        return category.to_dict() if category else None

def add_category(name, parent_id=None):
    with get_session() as session:
        category = Category(name=name, parent_id=parent_id)
        session.add(category)
        session.commit()
        return category.to_dict(include_relationships=False)

def delete_category(category_id):
    with get_session() as session:
        category = session.query(Category).filter(Category.id == category_id).first()
        if not category:
            return
        if session.query(Category).filter(Category.parent_id == category.id).count() > 0:
            raise exceptions.DBQueryException("该分类下有子分类，禁止删除")
        if session.query(Product).filter(Product.category_id == category.id).count() > 0:
            raise exceptions.DBQueryException("该分类下有商品，禁止删除")
        session.delete(category)
        session.commit()
        return category.to_dict(include_relationships=False)

def get_suppliers(name_or_contact="", phone="", page=1, per_page=config.DEFAULT_PAGE_SIZE):
    with get_session() as session:
        query = session.query(Supplier)
        if name_or_contact:
            query = query.filter(Supplier.name.like(f'%{name_or_contact}%') | Supplier.contact.like(f'%{name_or_contact}%'))
        if phone:
            query = query.filter(Supplier.phone.like(f'%{phone}%'))
        
        include_relationships = False
        count = query.count()
        if page and per_page:
            query = query.offset((page - 1) * per_page).limit(per_page)
            #include_relationships = True
        return {
            'count': count,
            'items': [supplier.to_dict(include_relationships=include_relationships) for supplier in query.all()],
            'page': page,
            'per_page': per_page
        }

def get_supplier(supplier_id):
    with get_session() as session:
        supplier = session.query(Supplier).filter(Supplier.id == supplier_id).first()
        return supplier.to_dict(include_relationships=False) if supplier else None

def add_supplier(name, contact, phone, wechat="", address=""):
    with get_session() as session:
        supplier = Supplier(name=name, contact=contact or name, phone=phone, wechat=wechat, address=address)
        session.add(supplier)
        session.commit()
        return supplier.to_dict(include_relationships=False)

def delete_supplier(supplier_id):
    with get_session() as session:
        supplier = session.query(Supplier).filter(Supplier.id == supplier_id).first()
        if not supplier:
            return
        if session.query(Purchase).filter(Purchase.supplier_id == supplier.id).count() > 0:
            raise exceptions.DBQueryException("该供应商下有采购单，禁止删除")
        session.query(ProductSupplier).filter(ProductSupplier.supplier_id == supplier.id).delete()
        session.delete(supplier)
        session.commit()
        return supplier.to_dict(include_relationships=False)

def update_supplier(supplier_id, name="", contact="", phone="", wechat="", address=""):
    with get_session() as session:
        supplier = session.query(Supplier).filter(Supplier.id == supplier_id).first()
        if not supplier:
            return
        if name:
            supplier.name = name
        if contact:
            supplier.contact = contact
        if phone:
            supplier.phone = phone
        if wechat:
            supplier.wechat = wechat
        if address:
            supplier.address = address
        session.commit()
        return supplier.to_dict(include_relationships=False)

def get_sales(code="", product_id=None, customer_id=None, status=None, user_id=None, page=1, per_page=config.DEFAULT_PAGE_SIZE,
              order_by="", range_date=None):
    with get_session() as session:
        query = session.query(Sale)
        if code:
            query = query.filter(Sale.code.like(f'%{code}%'))
        if product_id:
            query = query.join(SaleItem).filter(SaleItem.product_id == product_id)
        if customer_id:
            query = query.filter(Sale.customer_id == customer_id)
        if user_id:
            query = query.filter(Sale.user_id == user_id)
        if status:
            query = query.filter(Sale.status == status)
        if range_date:
            if type(range_date) not in (list, tuple):
                raise exceptions.DBQueryException("不是有效的时间范围")
            start = range_date[0]
            end = range_date[1] if len(range_date) > 1 else ''
            if start and end:
                query = query.filter(and_(Sale.created_at >= start, Sale.created_at <= end))
            elif start:
                query = query.filter(Sale.created_at >= start)
            elif end:
                query = query.filter(Sale.created_at <= end)
        
        only_relationships = ["items", "saleReturns", "product", "customer"]
        count = query.count()
        if order_by:
            if order_by.startswith("-"):
                order_by = order_by[1:]
                if order_by in config.SALE_ALLOW_SORT_COLUMNS:
                    query = query.order_by(desc(order_by))
            else:
                if order_by in config.SALE_ALLOW_SORT_COLUMNS:
                    query = query.order_by(order_by)
        if page and per_page:
            query = query.offset((page - 1) * per_page).limit(per_page)
            query = query.options(joinedload(Sale.items).joinedload(SaleItem.product), joinedload(Sale.customer))
        return {
            'count': count,
            'items': [sale.to_dict(only_relationships=only_relationships) for sale in query.all()],
            'page': page,
            'per_page': per_page
        }

def get_sale(id_or_code):
    with get_session() as session:
        query = session.query(Sale).options(
            joinedload(Sale.customer),
            joinedload(Sale.items).joinedload(SaleItem.product)
        )
        if utils.match_sale_code(id_or_code):
            query = query.filter(Sale.code == id_or_code)
        else:
            query = query.filter(Sale.id == id_or_code)
        sale = query.first()
        return sale.to_dict(only_relationships=["customer", "items", "product"]) if sale else None

def add_sale(customer_id, user_id, items):
    with get_session() as session:
        total_price = sum(item['quantity'] * item['unit_price'] for item in items)
        sale = Sale(
            code=utils.sale_code_generator(),
            customer_id=customer_id,
            user_id=user_id,
            total_price=total_price,
            status=utils.SaleStatus.UNPAID
        )
        session.add(sale)
        
        for item in items:
            sale_item = SaleItem(
                sale=sale,
                product_id=item['product_id'] if 'product_id' in item else item['product']['id'],
                quantity=item['quantity'],
                unit_price=item['unit_price'],
                total_price=item['quantity'] * item['unit_price']
            )
            session.add(sale_item)
        
        session.commit()
        return sale.to_dict()

def update_sale(id_or_code, status=None, payment_method=None):
    with get_session() as session:
        if utils.match_sale_code(id_or_code):
            sale = session.query(Sale).filter(Sale.code == id_or_code).first()
        else:
            sale = session.query(Sale).filter(Sale.id == id_or_code).first()
        if not sale:
            return
        if status:
            sale.status = status
        if status == utils.SaleStatus.PAID:
            if not payment_method:
                raise exceptions.DBQueryException("未指定支付方式")
            sale.payment_method = payment_method
            for item in sale.items:
                sale_quantity = item.quantity
                product = session.query(Product).filter(Product.id == item.product_id).with_for_update().first()
                if product.stock < sale_quantity:
                    raise exceptions.DBQueryException(f"商品 {product.name} 库存不足")
                
                batches = session.query(PurchaseBatch)\
                                .filter(PurchaseItem.product_id == item.product_id,
                                        PurchaseBatch.quantity_available > 0)\
                                .join(PurchaseItem, PurchaseBatch.purchase_item_id == PurchaseItem.id)\
                                .order_by(PurchaseBatch.created_at.asc())\
                                .with_for_update()\
                                .all()

                for batch in batches:
                    if sale_quantity <= 0:
                        break
                    used_quantity = min(batch.quantity_available, sale_quantity)
                    sale_item_batch = SaleItemBatch(
                        sale_item_id=item.id,
                        purchase_batch_id=batch.id,
                        quantity=used_quantity,
                        cost_price=batch.cost_price,
                        total_cost=used_quantity * batch.cost_price
                    )
                    session.add(sale_item_batch)
                    batch.quantity_available -= used_quantity
                    sale_quantity -= used_quantity

                if sale_quantity > 0:
                    raise exceptions.DBQueryException(f"商品 {product.name} 出库数量不足，批次不够")
                item.product.stock -= item.quantity
        sale.updated_at = datetime.now()
        session.commit()
        return sale.to_dict()
    
def update_sale_items(id_or_code, items):
    if not items:
        raise exceptions.DBQueryException("不允许更新成空的销售列表,如果此单作废请关闭交易")
    with get_session() as session:
        if utils.match_sale_code(id_or_code):
            sale = session.query(Sale).filter(Sale.code == id_or_code).first()
        else:
            sale = session.query(Sale).filter(Sale.id == id_or_code).first()
        if not sale:
            return
        old_products = {item.product_id: item for item in session.query(SaleItem).filter(SaleItem.sale_id == sale.id)}
        new_products = {int(item['product_id']) if 'product_id' in item else int(item['product']['id']): item for item in items}
        items_updated = False
        for product_id in new_products:
            if new_products[product_id]['quantity'] <= 0:
                raise exceptions.DBQueryException(f"不允许提交{new_products['quantity']}件商品的采购项")
            if 'total_price' in new_products[product_id]:
                new_products[product_id]['total_price'] = new_products[product_id]['quantity'] * new_products[product_id]['unit_price']
            if product_id not in old_products:
                session.add(SaleItem(sale=sale, product_id=product_id,
                                     quantity=new_products[product_id]['quantity'],
                                     unit_price=new_products[product_id]['unit_price'],
                                     total_price=new_products[product_id]['total_price']))
                items_updated = True
            elif old_products[product_id].unit_price != new_products[product_id]['unit_price']:  # 单价有变化 
                session.add(SaleItem(sale=sale, product_id=product_id,
                                     quantity=new_products[product_id]['quantity'],
                                     unit_price=new_products[product_id]['unit_price'],
                                     total_price=new_products[product_id]['total_price']))
                items_updated = True
            elif old_products[product_id].quantity != new_products[product_id]['quantity']:
                session.query(SaleItem).filter(SaleItem.id == old_products[product_id].id).update({
                    'quantity': new_products[product_id]['quantity'],
                    'unit_price': new_products[product_id]['unit_price'],
                    'total_price': new_products[product_id]['total_price']
                })
                items_updated = True
        deleted_item_ids = [old_products[product_id].id for product_id in old_products if product_id not in new_products]
        if deleted_item_ids:
            session.query(SaleItem).filter(SaleItem.id.in_(deleted_item_ids)).delete()
            items_updated = True
        if items_updated:
            sale.total_price = sum(item['total_price'] for item in items) if items else 0
            sale.updated_at = datetime.now()
        session.commit()
        return sale.to_dict(include_relationships=False)

def delete_sale(code):
    with get_session() as session:
        sale = session.query(Sale).filter(Sale.code == code).first()
        if not sale:
            return
        session.delete(sale)
        session.commit()
        return sale.to_dict()

def get_purchases(code="", supplier_id=None, status=None, product_id=None, page=1, per_page=config.DEFAULT_PAGE_SIZE,
                  order_by="", range_date=None):
    with get_session() as session:
        query = session.query(Purchase)
        if code:
            query = query.filter(Purchase.code.like(f'%{code}%'))
        if supplier_id:
            query = query.filter(Purchase.supplier_id == supplier_id)
        if status:
            query = query.filter(Purchase.status == status)
        if product_id:
            query = query.join(PurchaseItem).filter(PurchaseItem.product_id == product_id)
        if range_date:
            if type(range_date) not in (list, tuple):
                raise exceptions.DBQueryException("不是有效的时间范围")
            start = range_date[0]
            end = range_date[1] if len(range_date) > 1 else ''
            if start and end:
                query = query.filter(and_(Purchase.created_at >= start, Purchase.created_at <= end))
            elif start:
                query = query.filter(Purchase.created_at >= start)
            elif end:
                query = query.filter(Purchase.created_at <= end)
        
        only_relationships = []
        count = query.count()
        if order_by:
            if order_by.startswith("-"):
                order_by = order_by[1:]
                if order_by in config.PURCHASE_ALLOW_SORT_COLUMNS:
                    query = query.order_by(desc(order_by))
            else:
                if order_by in config.PURCHASE_ALLOW_SORT_COLUMNS:
                    query = query.order_by(order_by)
        if page and per_page:
            query = query.offset((page - 1) * per_page).limit(per_page)
            query = query.options(joinedload(Purchase.items).joinedload(PurchaseItem.product), joinedload(Purchase.supplier))
            only_relationships = ["supplier", "items", "product", "purchaseReturns"]
        return {
            'count': count,
            'items': [purchase.to_dict(only_relationships=only_relationships) if only_relationships
                      else purchase.to_dict(include_relationships=False) for purchase in query.all()],
            'page': page,
            'per_page': per_page
        }

def update_purchase(id_or_code, status=None):
    with get_session() as session:
        if utils.match_purchase_code(id_or_code):
            purchase = session.query(Purchase).filter(Purchase.code == id_or_code).first()
        else:
            purchase = session.query(Purchase).filter(Purchase.id == id_or_code).first()
        if not purchase:
            return
        if purchase.status == utils.PurchaseStatus.RECEIVED:
            raise exceptions.DBQueryException("该采购单已收货，禁止修改")
        if status:
            purchase.status = status
        if status == utils.PurchaseStatus.RECEIVED:
            for item in purchase.items:
                session.query(Product).filter(Product.id == item.product_id).update({
                    "stock" : Product.stock + item.quantity,
                    "updated_at": datetime.now()
                })
                batch = PurchaseBatch(
                    purchase_item_id=item.id,
                    quantity_total=item.quantity,
                    quantity_available=item.quantity,
                    cost_price=item.unit_price
                )
                session.add(batch)
        purchase.updated_at = datetime.now()
        session.commit()
        return purchase.to_dict()

def update_purchase_items(id_or_code, items):
    with get_session() as session:
        if utils.match_purchase_code(id_or_code):
            purchase = session.query(Purchase).filter(Purchase.code == id_or_code).first()
        else:
            purchase = session.query(Purchase).filter(Purchase.id == id_or_code).first()
        if not purchase:
            return
        if purchase.status != utils.PurchaseStatus.UNAUDITED:
            raise exceptions.DBQueryException("只允许修改未审核的采购单")
        # !important 此处不要用purchase.items去遍历, 因为如果子查询有delete操作on_update会报PurchaseItem已经删除的错误
        old_products = { item.product_id: item for item in session.query(PurchaseItem).filter(PurchaseItem.purchase_id == purchase.id) }
        new_products = { int(item['product_id']) if 'product_id' in item else int(item['product']['id']): item for item in items }
        items_updated = False
        for product_id in new_products:  #通过遍历新的商品列表，来更新或添加商品
            if new_products[product_id]['quantity'] <= 0:
                raise exceptions.DBQueryException(f"不允许提交{new_products['quantity']}件商品的采购项")
            if 'total_price' in new_products[product_id]:
                new_products[product_id]['total_price'] = new_products[product_id]['quantity'] * new_products[product_id]['unit_price']
            if product_id not in old_products:
                session.add(PurchaseItem(purchase=purchase, product_id=product_id,
                                         quantity=new_products[product_id]['quantity'],
                                         unit_price=new_products[product_id]['unit_price'],
                                         total_price=new_products[product_id]['total_price']))
                items_updated = True
            elif old_products[product_id].quantity != new_products[product_id]['quantity']: # 数量有变化
                session.query(PurchaseItem).filter(PurchaseItem.id == old_products[product_id].id).update({
                    'quantity': new_products[product_id]['quantity'],
                    'unit_price': new_products[product_id]['unit_price'],
                    'total_price': new_products[product_id]['total_price']
                })
                items_updated = True
        #通过遍历旧的商品列表，来删除已经不需要的商品
        deleted_item_ids = [old_products[product_id].id for product_id in old_products if product_id not in new_products] 
        if deleted_item_ids:
            session.query(PurchaseItem).filter(PurchaseItem.id.in_(deleted_item_ids)).delete()
            items_updated = True
        if items_updated:
            purchase.total_price = sum(item['total_price'] for item in items) if items else 0
            purchase.updated_at = datetime.now()
        session.commit()
        return purchase.to_dict(include_relationships=False)

def delete_purchase(id_or_code):
    with get_session() as session:
        if utils.match_purchase_code(id_or_code):
            purchase = session.query(Purchase).filter(Purchase.code == id_or_code).first()
        else:
            purchase = session.query(Purchase).filter(Purchase.id == id_or_code).first()
        if not purchase:
            return
        if purchase.status != utils.PurchaseStatus.UNAUDITED:
            raise exceptions.DBQueryException("只允许删除未审核的采购单")
        session.query(PurchaseItem).filter(PurchaseItem.purchase_id == purchase.id).delete()
        session.delete(purchase)
        session.commit()
        return purchase.to_dict(include_relationships=False)

def get_purchase(id_or_code):
    with get_session() as session:
        query = session.query(Purchase).options(
            joinedload(Purchase.supplier),
            joinedload(Purchase.items).joinedload(PurchaseItem.product)
        )
        if utils.match_purchase_code(id_or_code):
            query = query.filter(Purchase.code == id_or_code)
        else:
            query = query.filter(Purchase.id == id_or_code)
        purchase = query.first()
        return purchase.to_dict(only_relationships=["supplier", "items", "product", "purchaseReturns"]) if purchase else None

def add_purchase(supplier_id, user_id, items):
    with get_session() as session:
        # 计算订单总价
        total_price = sum(item['total_price'] if 'total_price' in item else item['quantity'] * item['unit_price'] for item in items)
        
        purchase = Purchase(
            code=utils.purchase_code_generator(),
            supplier_id=supplier_id,
            user_id=user_id,
            status=utils.PurchaseStatus.UNAUDITED,
            total_price=total_price
        )
        session.add(purchase)
        
        for item in items:
            if item['quantity'] <= 0:
                raise exceptions.DBQueryException(f"不允许提交{item['quantity']}件商品的采购项")
            purchase_item = PurchaseItem(
                purchase=purchase,
                product_id=item['product_id'] if 'product_id' in item else item['product']['id'],
                quantity=item['quantity'],
                unit_price=item['unit_price'],
                total_price=item['total_price'] if 'total_price' in item else item['quantity'] * item['unit_price'],
            )
            session.add(purchase_item)
        
        session.commit()
        return purchase.to_dict()

def get_customers(contact="", phone="", extras=False, page=1, per_page=config.DEFAULT_PAGE_SIZE):
    with get_session() as session:
        query = session.query(Customer)
        if contact:
            query = query.filter(or_(Customer.contact.like(f'%{contact}%'), Customer.wechat.like(f'%{contact}%')))
        if phone:
            query = query.filter(Customer.phone.like(f'%{phone}%'))

        count = query.count()
        if page and per_page:
            if extras:
                query = query.outerjoin(Sale, and_(Sale.status == utils.SaleStatus.PAID,
                                                   Sale.customer_id == Customer.id)).group_by(Customer.id).with_entities(
                    Customer.id,
                    Customer.contact,
                    Customer.phone,
                    Customer.wechat,
                    Customer.created_at,
                    func.coalesce(func.sum(Sale.total_price), 0).label("total_amount")
                )
            query = query.offset((page - 1) * per_page).limit(per_page)
        if extras:
            items = [dict(row._mapping) for row in query.all()]
        else:
            items = [customer.to_dict(include_relationships=False) for customer in query.all()]
        return {
            'count': count,
            'items': items,
            'page': page,
            'per_page': per_page
        }

def get_customer(id):
    with get_session() as session:
        customer = session.query(Customer).filter(Customer.id == id).first()
        return customer.to_dict() if customer else None

def add_customer(contact, phone, wechat=""):
    with get_session() as session:
        customer = Customer(contact=contact, phone=phone, wechat=wechat)
        session.add(customer)
        session.commit()
        return customer.to_dict()

def delete_customer(customer_id):
    with get_session() as session:
        customer = session.query(Customer).filter(Customer.id == customer_id).first()
        if not customer:
            return
        if session.query(Sale).filter(Sale.customer_id == customer.id).count() > 0:
            raise exceptions.DBQueryException("该客户下有销售单，禁止删除")
        session.delete(customer)
        session.commit()
        return customer.to_dict()

def update_customer(customer_id, contact, phone, wechat=""):
    with get_session() as session:
        customer = session.query(Customer).filter(Customer.id == customer_id).first()
        if not customer:
            return
        if contact:
            customer.contact = contact
        if phone:
            customer.phone = phone
        if wechat:
            customer.wechat = wechat
        session.commit()
        return customer.to_dict()

def get_logs(dateOrRange=None, page=1, per_page=config.DEFAULT_PAGE_SIZE):
    if not page and not per_page:
        raise exceptions.DBQueryException("查询日志必须分页查询")
    with get_session() as session:
        query = session.query(Log)
        if dateOrRange:
            if isinstance(dateOrRange, str):
                query = query.filter(Log.created_at == dateOrRange)
            elif isinstance(dateOrRange, list) and len(dateOrRange) == 2:
                if dateOrRange[0]:
                    query = query.filter(Log.created_at >= dateOrRange[0])
                if dateOrRange[1]:
                    query = query.filter(Log.created_at <= dateOrRange[1])
            else:
                raise exceptions.DBQueryException("日期格式错误")

        count = query.count()
        query = query.offset((page - 1) * per_page).limit(per_page)
        items = [log.to_dict() for log in query.all()]
        return {
            'count': count,
            'items': items,
            'page': page,
            'per_page': per_page
        }
            
def add_log(user_id, details, relations={}):
    if not isinstance(relations, dict):
        raise exceptions.DBQueryException("关联关系数据格式错误")
    with get_session() as session:
        log = Log(user_id=user_id, details=details)
        session.add(log)
        for table_name, record_id in relations.items():
            relation_log = LogRelation(log_id=log.id, table_name=table_name, record_id=record_id)
            session.add(relation_log)
        session.commit()
        return log.to_dict()

def get_count(table, filter):
    ## 统计指定表的数据数量
    table_mapper = {
        'purchases': Purchase,
        'sales': Sale,
        'products': Product,
        'purchase_returns': PurchaseReturn,
        'sale_returns': SaleReturn,
        'purchase_returns_items': PurchaseReturnItem,
}
    with get_session() as session:
        table_class = table_mapper.get(table)
        if not table_class:
            raise exceptions.DBQueryException("不支持该表的统计")
        query = session.query(table_class)
        if filter:
            for field, value in filter.items():
                if isinstance(value, list) and len(value) == 2:
                    if value[0]:
                        query = query.filter(getattr(table_class, field) >= value[0])
                    if value[1]:
                        query = query.filter(getattr(table_class, field) <= value[1])
                    continue
                if isinstance(value, str) and any(op in field for op in [" >= ", " <= ", " == "]):
                    column1, op, column2 = field.split()
                    if op == '>':
                        query = query.filter(getattr(table_class, column1) >= getattr(table_class, column2))
                    elif op == '<':
                        query = query.filter(getattr(table_class, column1) <= getattr(table_class, column2))
                    else:
                        query = query.filter(getattr(table_class, column1) == getattr(table_class, column2))
                    continue
                query = query.filter(getattr(table_class, field) == value)
        return query.count()

def get_total_price(table, filter):
    ## 统计采购/销售/退货的总金额
    from sqlalchemy import func
    
    table_mapper = {
        'purchases': Purchase,
        'sales': Sale,
        'purchase_returns': PurchaseReturn,
        'sale_returns': SaleReturn,
        'purchase_return_items': PurchaseReturnItem,
        'sale_return_items': SaleReturnItem
    }
    
    with get_session() as session:
        table_class = table_mapper.get(table)
        if not table_class:
            raise exceptions.DBQueryException("不支持该表的统计")
        
        # 使用func.sum聚合函数计算total_price总和
        query = session.query(func.sum(table_class.total_price))
        
        # 应用过滤条件
        if filter:
            for field, value in filter.items():
                # 处理范围查询，例如日期范围
                if isinstance(value, list) and len(value) == 2:
                    if value[0]:
                        query = query.filter(getattr(table_class, field) >= value[0])
                    if value[1]:
                        query = query.filter(getattr(table_class, field) <= value[1])
                    continue
                # 处理精确匹配
                query = query.filter(getattr(table_class, field) == value)
        
        # 执行查询并获取结果
        result = query.scalar()
        return result if result is not None else 0
    
def get_purchase_returns(purchase_id_or_code=None):
    with get_session() as session:
        query = session.query(PurchaseReturn)
        if utils.match_purchase_code(purchase_id_or_code):
            purchase = session.query(Purchase).filter(Purchase.code == purchase_id_or_code).first()
            if not purchase:
                raise exceptions.DBQueryException("采购单不存在")
            query = query.filter(PurchaseReturn.purchase_id == purchase.id)
        else:
            query = query.filter(PurchaseReturn.purchase_id == purchase_id_or_code)
        only_relationships = ["items", "purchaseItem", "product"]
        query = query.options(joinedload(PurchaseReturn.items).joinedload(PurchaseReturnItem.purchaseItem).joinedload(PurchaseItem.product))
        return [purchase_return.to_dict(only_relationships=only_relationships, max_depth=3) for purchase_return in query.all()]
    
def add_purchase_return(purchase_code_or_id, user_id, items, reason="", return_date=datetime.now()):
    with get_session() as session:
        if utils.match_purchase_code(purchase_code_or_id):
            purchase = session.query(Purchase).filter(Purchase.code == purchase_code_or_id).first()
        else:
            purchase = session.query(Purchase).filter(Purchase.id == purchase_code_or_id).first()
        if not purchase:
            raise exceptions.DBQueryException("采购单不存在")
        if any(item['purchase_item_id'] not in [item.id for item in purchase.items] for item in items):
            raise exceptions.DBQueryException("有不在采购单的退货项, 不能开据退单")
        total_price = sum(item['total_price'] if 'total_price' in item else item['quantity'] * item['unit_price'] for item in items)
        purchase_return = PurchaseReturn(
            purchase_id=purchase.id,
            user_id=user_id,
            reason=reason,
            return_date=return_date,
            total_price=total_price
        )
        session.add(purchase_return)
        for item in items:
            purchase_return_item = PurchaseReturnItem(
                purchaseReturn=purchase_return,
                purchase_item_id=item['purchase_item_id'],
                quantity=item['quantity'],
                unit_price=item['unit_price'],
                total_price=item['total_price'] if 'total_price' in item else item['quantity'] * item['unit_price']
            )
            session.add(purchase_return_item)
            session.query(Product).filter(Product.id == item['product']['id']).update({
                'stock': Product.stock - item['quantity'],
                'updated_at': datetime.now()
            })
        session.commit()
        return purchase_return.to_dict(include_relationships=False)
    
def get_sale_returns(sale_id_or_code=None):
    with get_session() as session:
        query = session.query(SaleReturn)
        if utils.match_sale_code(sale_id_or_code):
            sale = session.query(Sale).filter(Sale.code == sale_id_or_code).first()
            if not sale:
                raise exceptions.DBQueryException("销售单不存在")
            query = query.filter(SaleReturn.sale_id == sale.id)
        else:
            query = query.filter(SaleReturn.sale_id == sale_id_or_code)
        only_relationships = ["items", "saleItem", "product"]
        query = query.options(joinedload(SaleReturn.items).joinedload(SaleReturnItem.saleItem).joinedload(SaleItem.product))
        return [sale_return.to_dict(only_relationships=only_relationships, max_depth=3) for sale_return in query.all()]

def add_sale_return(sale_code_or_id, user_id, items, reason="", return_date=datetime.now()):
    with get_session() as session:
        if utils.match_sale_code(sale_code_or_id):
            sale = session.query(Sale).filter(Sale.code == sale_code_or_id).first()
        else:
            sale = session.query(Sale).filter(Sale.id == sale_code_or_id).first()
        if not sale:
            raise exceptions.DBQueryException("销售单不存在")
        if any(item['sale_item_id'] not in [item.id for item in sale.items] for item in items):
            raise exceptions.DBQueryException("有不在销售单的退货项, 不能开据退单")
        total_price = sum(item['total_price'] if 'total_price' in item else item['quantity'] * item['unit_price'] for item in items)
        sale_return = SaleReturn(
            sale_id=sale.id,
            user_id=user_id,
            reason=reason,
            return_date=return_date,
            total_price=total_price
        )
        session.add(sale_return)
        for item in items:
            sale_return_item = SaleReturnItem(
                saleReturn=sale_return,
                sale_item_id=item['sale_item_id'],
                quantity=item['quantity'],
                unit_price=item['unit_price'],
                total_price=item['total_price'] if 'total_price' in item else item['quantity'] * item['unit_price']
            )
            session.add(sale_return_item)
        session.commit()
        return sale_return.to_dict()
    
def get_supplier_products(supplier_id):
    with get_session() as session:
        query = session.query(ProductSupplier).options(joinedload(ProductSupplier.product))
        return [ps.to_dict(only_relationships=["product"], max_depth=1) for ps in query.filter(ProductSupplier.supplier_id == supplier_id).all()]
    
def search_supplier_products(supplier_id, search):
    with get_session() as session:
        query = session.query(ProductSupplier).join(Product, ProductSupplier.supplier_id == supplier_id)
        if utils.match_product_code(search):
            query = query.filter(Product.code == search)
        elif search.isdigit():
            query = query.filter(Product.code.like(f'%{search}%'))
        elif search:
            query = query.filter(Product.name.like(f'%{search}%'))
        query = query.options(joinedload(ProductSupplier.product))
        products = []
        for ps in query.all():
            product = ps.to_dict(only_relationships=["product"], max_depth=1)['product']
            product['cost_price'] = ps.cost_price
            products.append(product)
        return products

def update_supplier_products(supplier_id, products):
    """products参数格式 [{'id': 1, 'cost_price': 100}, {'id': 2, 'cost_price': 200}]"""
    new_products_dict = {p['id']: p for p in products}
    new_product_ids = set(p['id'] for p in products)
    with get_session() as session:
        old_supplier_products = {ps.product_id: ps for ps in session.query(ProductSupplier).filter(ProductSupplier.supplier_id == supplier_id).all()}
        old_product_ids = set(old_supplier_products.keys())
        for product_id in new_product_ids - old_product_ids: # 新增
            session.add(ProductSupplier(supplier_id=supplier_id, product_id=product_id, cost_price=new_products_dict[product_id]['cost_price']))
        for product_id in new_product_ids & old_product_ids: # 成本价变动
            if old_supplier_products[product_id].cost_price != new_products_dict[product_id]['cost_price']:
                session.query(ProductSupplier).filter(ProductSupplier.id == old_supplier_products[product_id].id).update({
                    'cost_price': new_products_dict[product_id]['cost_price']
                })
        deleted_product_ids = old_product_ids - new_product_ids # 删除
        if deleted_product_ids:
            session.query(ProductSupplier).filter(ProductSupplier.supplier_id == supplier_id,
                                                  ProductSupplier.product_id.in_(deleted_product_ids)).delete()
        session.commit()
        return True
    
def get_product_suppliers(product_id):
    with get_session() as session:
        product = session.query(Product).filter(Product.id == product_id).first()
        if not product:
            raise exceptions.DBQueryException("商品不存在")
        product_info = product.to_dict(include_relationships=False)
        product_info['suppliers'] = []
        query = session.query(ProductSupplier).options(joinedload(ProductSupplier.supplier))
        for ps in query.filter(ProductSupplier.product_id == product_id).all():
            supplier = ps.to_dict(only_relationships=["supplier"], max_depth=1)['supplier']
            supplier['cost_price'] = ps.cost_price
            product_info['suppliers'].append(supplier)
        return product_info

def calculate_profit_detail(start_date=None, end_date=None, customer_id=None, product_id=None):
    """
    查询某段时间内，每个商品的销售量、销售额、成本额、利润额。
    :return: List[dict]
    """
    with get_session() as session:
        query = session.query(
            Product.id.label("product_id"),
            Product.code.label("product_code"),
            Product.name.label("product_name"),
            func.sum(SaleItem.quantity).label("total_quantity"),
            func.sum(SaleItem.unit_price * SaleItem.quantity).label("sales_total"),
            func.sum(SaleItemBatch.total_cost).label("cost_total")
        ).join(SaleItem, SaleItem.product_id == Product.id)\
        .join(SaleItemBatch, SaleItemBatch.sale_item_id == SaleItem.id)\
        .join(Sale, Sale.id == SaleItem.sale_id)

        filters = [
            Sale.status == "PAID"
        ]

        if start_date:
            filters.append(Sale.sale_date >= start_date)
        if end_date:
            filters.append(Sale.sale_date <= end_date)
        if customer_id:
            filters.append(Sale.customer_id == customer_id)
        if product_id:
            filters.append(Product.id == product_id)

        query = query.filter(and_(*filters))
        query = query.group_by(Product.id, Product.code, Product.name)

        results = query.all()

        table = []
        for row in results:
            sales_total = float(row.sales_total or 0)
            cost_total = float(row.cost_total or 0)
            profit_total = sales_total - cost_total

            table.append({
                "product_id": row.product_id,
                "product_code": row.product_code,
                "product_name": row.product_name,
                "total_quantity": int(row.total_quantity or 0),
                "sales_total": sales_total,
                "cost_total": cost_total,
                "profit_total": profit_total
            })
        return table
    
def calculate_top_profit_products(start_date=None, end_date=None, customer_id=None, top_n=10):
    """
    查询某段时间内利润最高的Top N个商品
    :param start_date: 开始日期
    :param end_date: 结束日期
    :param customer_id: 指定客户ID（可选）
    :param top_n: 取前多少条，默认10
    :return: List[dict]
    """
    with get_session() as session:
        query = session.query(
            Product.id.label("product_id"),
            Product.code.label("product_code"),
            Product.name.label("product_name"),
            func.sum(SaleItem.quantity).label("total_quantity"),
            func.sum(SaleItem.unit_price * SaleItem.quantity).label("sales_total"),
            func.sum(SaleItemBatch.total_cost).label("cost_total"),
            (func.sum(SaleItem.unit_price * SaleItem.quantity) - func.sum(SaleItemBatch.total_cost)).label("profit_total")
        ).join(SaleItem, SaleItem.product_id == Product.id)\
         .join(SaleItemBatch, SaleItemBatch.sale_item_id == SaleItem.id)\
         .join(Sale, Sale.id == SaleItem.sale_id)

        filters = [Sale.status == "PAID"]

        if start_date:
            filters.append(Sale.sale_date >= start_date)
        if end_date:
            filters.append(Sale.sale_date <= end_date)
        if customer_id:
            filters.append(Sale.customer_id == customer_id)

        query = query.filter(and_(*filters))
        query = query.group_by(Product.id, Product.code, Product.name)
        query = query.order_by(desc("profit_total"))  # 🔥 按利润总额倒序
        query = query.limit(top_n)

        results = query.all()

        table = []
        for row in results:
            sales_total = row.sales_total or 0
            cost_total = row.cost_total or 0
            profit_total = row.profit_total or 0

            table.append({
                "product_id": row.product_id,
                "product_code": row.product_code,
                "product_name": row.product_name,
                "total_quantity": int(row.total_quantity or 0),
                "sales_total": sales_total,
                "cost_total": cost_total,
                "profit_total": profit_total
            })

        return table
    
    
def get_sales_chart_data(range_date="week"):
    """
    获取销售数据的图表数据
    :param range_date: "week", "month", "year"
    :return: dict
    """
    with get_session() as session:
        now = datetime.now()
        if range_date == "week":
            start_date = now - timedelta(days=now.weekday())
            end_date = now + timedelta(days=6 - now.weekday())
            query = session.query(
                func.extract('day', Sale.sale_date).label("date"),
                func.sum(Sale.total_price).label("totoal_price")
            ).filter(Sale.status == utils.SaleStatus.PAID)
            query = query.filter(Sale.sale_date >= start_date, Sale.sale_date <= end_date)
            query = query.group_by(func.extract('day', Sale.sale_date))
        elif range_date == "month":
            start_date = datetime(now.year, now.month, 1)
            end_date = datetime(now.year, now.month + 1, 1) if now.month < 12 else datetime(now.year + 1, 1, 1)
            query = session.query(
                func.extract('day', Sale.sale_date).label("date"),
                func.sum(Sale.total_price).label("totoal_price")
            ).filter(Sale.status == utils.SaleStatus.PAID)
            query = query.filter(Sale.sale_date >= start_date, Sale.sale_date < end_date)
            query = query.group_by(func.extract('day', Sale.sale_date))
        elif range_date == "year":
            start_date = datetime(now.year, 1, 1)
            end_date = datetime(now.year + 1, 1, 1)
            query = session.query(
                func.extract('month', Sale.sale_date).label("date"),
                func.sum(Sale.total_price).label("totoal_price")
            ).filter(Sale.status == utils.SaleStatus.PAID)
            query = query.filter(Sale.sale_date >= start_date, Sale.sale_date < end_date)
            query = query.group_by(func.extract('month', Sale.sale_date))
        else:
            raise exceptions.DBQueryException("不支持的时间范围")

        chart_data = {}
        for row in query.all():
            chart_data[row.date] = row.totoal_price or 0
        return chart_data
    
def get_purchases_chart_data(range_date="week"):
    """
    获取采购数据的图表数据
    :param range_date: "week", "month", "year"
    :return: dict
    """
    with get_session() as session:
        now = datetime.now()
        if range_date == "week":
            start_date = now - timedelta(days=now.weekday())
            end_date = now + timedelta(days=6 - now.weekday())
            query = session.query(
                func.extract('day', Purchase.created_at).label("date"),
                func.sum(Purchase.total_price).label("totoal_price")
            ).filter(Purchase.status == utils.PurchaseStatus.RECEIVED)
            query = query.filter(Purchase.created_at >= start_date, Purchase.created_at <= end_date)
            query = query.group_by(func.extract('day', Purchase.created_at))
        elif range_date == "month":
            start_date = datetime(now.year, now.month, 1)
            end_date = datetime(now.year, now.month + 1, 1) if now.month < 12 else datetime(now.year + 1, 1, 1)
            query = session.query(
                func.extract('day', Purchase.created_at).label("date"),
                func.sum(Purchase.total_price).label("totoal_price")
            ).filter(Purchase.status == utils.PurchaseStatus.RECEIVED)
            query = query.filter(Purchase.created_at >= start_date, Purchase.created_at < end_date)
            query = query.group_by(func.extract('day', Purchase.created_at))
        elif range_date == "year":
            start_date = datetime(now.year, 1, 1)
            end_date = datetime(now.year + 1, 1, 1)
            query = session.query(
                func.extract('month', Purchase.created_at).label("date"),
                func.sum(Purchase.total_price).label("totoal_price")
            ).filter(Purchase.status == utils.PurchaseStatus.RECEIVED)
            query = query.filter(Purchase.created_at >= start_date, Purchase.created_at < end_date)
            query = query.group_by(func.extract('month', Purchase.created_at))
        else:
            raise exceptions.DBQueryException("不支持的时间范围")

        chart_data = {}
        for row in query.all():
            chart_data[row.date] = row.totoal_price or 0
        return chart_data
    
def get_sales_return_chart_data(range_date="week"):
    """
    获取销售退货数据的图表数据
    :param range_date: "week", "month", "year"
    :return: dict
    """
    with get_session() as session:
        now = datetime.now()
        if range_date == "week":
            start_date = now - timedelta(days=now.weekday())
            end_date = now + timedelta(days=6 - now.weekday())
            query = session.query(
                func.extract('day', SaleReturn.return_date).label("date"),
                func.sum(SaleReturn.total_price).label("totoal_price")
            )
            query = query.filter(SaleReturn.return_date >= start_date, SaleReturn.return_date <= end_date)
            query = query.group_by(func.extract('day', SaleReturn.return_date))
        elif range_date == "month":
            start_date = datetime(now.year, now.month, 1)
            end_date = datetime(now.year, now.month + 1, 1) if now.month < 12 else datetime(now.year + 1, 1, 1)
            query = session.query(
                func.extract('day', SaleReturn.return_date).label("date"),
                func.sum(SaleReturn.total_price).label("totoal_price")
            )
            query = query.filter(SaleReturn.return_date >= start_date, SaleReturn.return_date < end_date)
            query = query.group_by(func.extract('day', SaleReturn.return_date))
        elif range_date == "year":
            start_date = datetime(now.year, 1, 1)
            end_date = datetime(now.year + 1, 1, 1)
            query = session.query(
                func.extract('month', SaleReturn.return_date).label("date"),
                func.sum(SaleReturn.total_price).label("totoal_price")
            )
            query = query.filter(SaleReturn.return_date >= start_date, SaleReturn.return_date < end_date)
            query = query.group_by(func.extract('month', SaleReturn.return_date))
        else:
            raise exceptions.DBQueryException("不支持的时间范围")

        chart_data = {}
        for row in query.all():
            chart_data[row.date] = row.totoal_price or 0
        return chart_data